/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Categories;
import com.tces.model.Questions;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class QuestionDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(QuestionDAO.class);

    /**
     * This will retrieve all the questions that is visible base on the @param categoryID
     * @param categoryID
     * @return active question list
     * @throws Exception 
     */
    public static List<Questions> getActiveQuestions(int categoryID) throws Exception {
        List<Questions> questionList = new ArrayList<Questions>();
        String sql = "SELECT\n"
                + "c.category_id,\n"
                + "c.categery_name,\n"
                + "c.category_percentage,\n"
                + "cq.categories_questions_order,\n"
                + "q.question_id,\n"
                + "q.question_descrption,\n"
                + "q.question_visible\n"
                + "FROM\n"
                + "categories AS c\n"
                + "INNER JOIN categories_questions AS cq ON cq.category_id = c.category_id\n"
                + "INNER JOIN questions AS q ON q.question_id = cq.question_id\n"
                + "WHERE c.category_visible=1\n"
                + "AND q.question_visible=1\n"
                + "AND c.category_id=?";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, categoryID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Questions question;
        while (rs.next()) {
            question = new Questions();
            question.setCategoryID(rs.getInt("category_id"));
            question.setCategoryPercentage(rs.getDouble("category_percentage"));
            question.setCategoryName(rs.getString("categery_name"));
            question.setQuestionDescription(rs.getString("question_descrption"));
            question.setQuestionID(rs.getInt("question_id"));
            question.setQuestionVisible(rs.getInt("question_visible"));
            question.setCategoriesQuestionsOrder(rs.getInt("categories_questions_order"));
            questionList.add(question);
        }
        closeConnection(getConnectionObject());
        return questionList;
    }
}